import time

import xlwings as xw
import os
import datetime

##设置代码从python中获取，而不是excel（vba）
xw.Book(os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))),'generatecase','generatecase.xlsm')).set_mock_caller()
##当vba通过runpython从excel中调用python函数时，引用这个book
wb = xw.Book.caller()
#获取sheet生成用例配置
shtcfg = wb.sheets['生成用例配置']
#获取sheet通用用例模板
shtcom = wb.sheets['通用用例模板']
#当前文档所有的sheet，存在列表中
delsheetname = ['生成用例配置', '通用用例模板', '_xlwings.conf']
#新增一个sheet
shtnew = xw.sheets.add()
###获取当前单元格所有数据，包括空数据,去掉第一列
datacfg = shtcfg.used_range.value[1:]
createbookname = shtcfg.range('H2').value
datacom = shtcom.range('a2').expand('table').value
comcaseline = int(shtcfg.range('I2').value) - 1
datacoml1 = shtcom.range('a1:l1').value
shtnew.range('a1').value=datacoml1
shtnew.range('a1').expand('right').api.Font.Color = 0xffffff  # 设置字体为白色
shtnew.range('a1').expand('right').color = (52, 51, 153)  # 设置填充色为蓝色
#以下是替换的关键代码
for i in range(len(datacfg)):
    for j in range(comcaseline):
        begin = 2 + i*comcaseline
        end = (comcaseline+1)*(i+1)
        range1 = 'a'+ str(begin) + ':l' + str(end)
        shtnew.range(range1).value = datacom
        shtnew.range(range1).api.Replace('通用所属项目',datacfg[i][0])
        shtnew.range(range1).api.Replace('通用研发需求',datacfg[i][1])
        shtnew.range(range1).api.Replace('一级目录',datacfg[i][2])
        shtnew.range(range1).api.Replace('二级目录',datacfg[i][3])
        if datacfg[i][4] == None:
            shtnew.range(range1).api.Replace('-三级目录','')
            shtnew.range(range1).api.Replace('-四级目录','')
        else:
            shtnew.range(range1).api.Replace('三级目录',datacfg[i][4])
            if datacfg[i][5] != None:
                shtnew.range(range1).api.Replace('四级目录',datacfg[i][5])
            else:
                shtnew.range(range1).api.Replace('-四级目录', '')
datanew = shtnew.range('a2').expand('table').value
for i in range(len(datanew)):
    if i %2 ==0:
        shtnew.range(i+2,1).expand('right').color = (178, 215, 234)
    else:
        shtnew.range(i+2,1).expand('right').color = (222, 230, 251)
#以下设置边框格式
for i in range(7,13):
    shtnew.range('A1').expand('table').api.Borders(i).LineStyle=1
shtnew.range('L1').expand('right').api.Borders(10).LineStyle=1
#设置每列列宽
shtnew.range('A1').column_width = 8.71
shtnew.range('B1').column_width = 10.38
shtnew.range('C1').column_width = 29.25
shtnew.range('D1').column_width = 17.4
shtnew.range('E1').column_width = 29.63
shtnew.range('F1').column_width = 29.63
shtnew.range('G1').column_width = 10.38
shtnew.range('H1').column_width = 6.88
shtnew.range('I1').column_width = 10.38
shtnew.range('J1').column_width = 10.38
shtnew.range('K1').column_width = 8.33
shtnew.range('L1').column_width = 10.38
###设置字体和字号
shtnew.range('A1').expand('table').api.Font.Name = '微软雅黑'
shtnew.range('A1').expand('table').api.Font.Size = 9
shtnew.range('A1').expand('right').api.Font.Bold = 1
##设置对齐方式
shtnew.range('A1').expand('right').api.HorizontalAlignment = -4108   # 水平位置：-4108 居中 -4131 靠左  -4152 靠右。
shtnew.range('A2').expand('table').api.VerticalAlignment = -4108   	 # 垂直位置：-4108 居中 -4160 靠上  -4107 靠下 -4130 自动换行对齐。
#关键字列,清空空格占用
shtnew.range('G2').expand().api.Replace('空格占用','')
createbookname = shtcfg.range('H2').value

if createbookname != None:
    filepath = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'generatecase', createbookname+'.xlsx')
else:
    filepath = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'generatecase', datetime.datetime.now().strftime('%Y%m%d%H%M%S')+'.xlsx')
###文件另存为，路径为filepath
wb.save(filepath)
wb.close()

#启动Excel程序窗口，但不新建工作簿。
app = xw.App(visible=True, add_book=False)
workbook = app.books.open(filepath)
for i in workbook.sheets:
    time.sleep(2)
    if i.name != 'Sheet2':
        workbook.sheets[i.name].delete()
#删除名称为sheet2以外的sheet
rng=workbook.sheets[0].range('A1').expand('table').api
rng.WrapText=True
#替换sheetname为测试用例
workbook.sheets.name.replace("Sheet2","测试用例")
workbook.save()
app.quit()